-- SQL for the words table
-- this table stores the words for the dictionary


-- clean the database before adding tables
DROP TABLE IF EXISTS Words;
DROP TABLE IF EXISTS Meanings;
DROP VIEW IF EXISTS Dictionary;

-- create table words
CREATE TABLE Words (Id INTEGER PRIMARY KEY,
 Name TEXT -- NAME OF WORD. Note :UNIQUE automatically creates an index
);

-- sql for the meanings table
-- this table stores the multiple meanings for each word
-- this table depends on the words table
CREATE TABLE Meanings (Id INTEGER PRIMARY KEY,
 Definition TEXT NOT NULL,
 Word_id INTEGER REFERENCES Words(Id));

-- create a virtual tabl dictionary with words and their multiple meanings
CREATE VIEW Dictionary AS
 SELECT Words.Id AS Id,
 Words.Name AS Name,
 Meanings.Definition AS Definition
 FROM Words
 INNER JOIN Meanings ON Words.Id = Meanings.Word_id;
